﻿#!/usr/bin/python

__author_="lidonglin"
__date__="$2012-7-12 11:25:54$"

from datetime import *
import json
import urllib, urllib2

def escape(str):
    retstr = ''
    for item in str:
        if (item.isdigit() or item.islower() or item.isupper()):
            retstr = retstr + item
        else:
            chord = ord(item)
            if (chord < 256):
                retstr = retstr + '%'
                if (chord < 16):
                    retstr = retstr + '0'
                retstr = retstr + "%X"%ord(item)
            else:
                retstr = retstr + '%u'
                retstr = retstr + "%X"%ord(item)
    return retstr

def connectGMServer(user, password, gmsvrurl):
    loginData = {'username':user, 'password':password}
    login = urllib2.urlopen(
            url = gmsvrurl + r'/login',
            data = json.dumps(loginData)            
        )
    loginRes = json.loads(login.read())
    if (loginRes['result']==0):
        sessionid = loginRes['sessionid']        
        return sessionid
    else:
        return ""

def createCouponDB(dbname, outputfile):    
    import MySQLdb
    conn = MySQLdb.connect(host='localhost',user='root',passwd='')
    cursor = conn.cursor()
    try :
        sql = "SELECT COUNT(1) FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = '" + dbname + "'"
        cursor.execute(sql)
        cursor.scroll(0)
        retrow = cursor.fetchone()
        if (retrow[0] == 0):
            sql = " CREATE DATABASE if not exists " + dbname + " default charset utf8 collate utf8_general_ci;"
            cursor.execute(sql)
            sql = " GRANT ALL ON " + dbname + ".* TO 'mythtest'@'192.168.200.%' IDENTIFIED BY 'jkY5qmGKVcRs4nST';"
            cursor.execute(sql)
            conn.close()            
            cursor.close()
            
            conn = MySQLdb.connect(host='localhost',user='root', db=dbname)
            cursor = conn.cursor()
            sql = """ CREATE TABLE `coupon` (                  
                  `couponcode` varchar(250) NOT NULL,
                  `cid` int(11) NULL DEFAULT NULL,
                  `time` timestamp NULL DEFAULT NULL,
                  `recvtime` timestamp NULL DEFAULT NULL
                ) ENGINE=MyISAM DEFAULT CHARSET=utf8; """
            cursor.execute(sql)
            conn.close()
            cursor.close()
            print "***DB " + dbname + " create success!!"
        else:
            print "***DB " + dbname + " already exists!!"
        return "true"
    except Exception, data:
        print Exception, ":", data
        outputfile.write('Exit: CreateCouponDB Exception--' + str(data) + '\n')
        return "false"

def getAvaibleCouponNum(dbname, outputfile):
    import MySQLdb
    conn = MySQLdb.connect(host='localhost',user='root',db=dbname)
    cursor = conn.cursor()
    try :
        sql = "SELECT COUNT(1) FROM coupon WHERE cid IS NULL"
        cursor.execute(sql)
        cursor.scroll(0)
        retrow = cursor.fetchone()
        conn.close()            
        cursor.close()
        return retrow[0]
    except Exception, data:
        print Exception, ":", data
        outputfile.write('Exit: GetAvaibleCouponNum Exception--' + str(data) + '\n')
        return None


def getCouponStartTime(dbname, starttime, outputfile):
    import MySQLdb
    conn = MySQLdb.connect(host='localhost',user='root',db=dbname)
    cursor = conn.cursor()
    try :
        sql = "SELECT MAX(TIME) FROM (SELECT TIME FROM coupon WHERE TIME IS NOT NULL) AS TimeList ORDER BY TIME;"
        cursor.execute(sql)
        cursor.scroll(0)
        retrow = cursor.fetchone()
        conn.close()            
        cursor.close()
        if (retrow[0] == None):            
            return starttime
        else:            
            dbtime = retrow[0]            
            if (dbtime > starttime):                
                return dbtime
            else:
                return starttime
    except Exception, data:
        print Exception, ":", data
        outputfile.write('Exit: GetCouponStartTime Exception--' + str(data) + '\n')
        return None

def getPlayerList(startime, endtime, logdbname, playerlevel, serverid, outputfile):
    import MySQLdb
    conn = MySQLdb.connect(host='localhost',user='root',db=logdbname)
    cursor = conn.cursor()
    try :
        sql = "SELECT cid, time FROM ACTION WHERE serverid = " + str(serverid) + " AND TIME > '" + str(startime) + "' AND TIME <= '" + str(endtime) + "' AND ACTION = 102 AND intdata1 < " + str(playerlevel) + " AND intdata2 >= " + str(playerlevel)
        cursor.execute(sql)        
        playerList = []
        for i in cursor.fetchall():
            playerList.append(i)
        conn.close()            
        cursor.close()
        return playerList
    except Exception, data:
        print Exception, ":", data
        outputfile.write('Exit: GetPlayerList Exception--' + str(data) + '\n')
        return None

def getCouponList(dbname, outputfile):
    import MySQLdb
    conn = MySQLdb.connect(host='localhost',user='root',db=dbname)
    cursor = conn.cursor()
    try :
        sql = "SELECT couponcode FROM coupon WHERE cid IS NULL"        
        cursor.execute(sql)        
        couponList = []
        for i in cursor.fetchall():
            couponList.append(i)
        conn.close()            
        cursor.close()
        return couponList
    except Exception, data:
        print Exception, ":", data
        outputfile.write('Exit: GetCouponList Exception--' + str(data) + '\n')
        return None

def executeSendCoupon(cid, occurtime, couponcode, dbname, curtime, sessionid, serverid, gmsvrurl, outputfile):
    import MySQLdb
    conn = MySQLdb.connect(host='localhost',user='root',db=dbname)
    cursor = conn.cursor()
    try :
        sql = "UPDATE coupon SET cid = " + str(cid) + ", time = '" + str(occurtime) + "', recvtime = '" + str(curtime) + "' WHERE couponcode = '" + couponcode + "'"        
        cursor.execute(sql)        
        outputfile.write('cid: ' + str(cid) + ' occurtime: ' + str(occurtime) + ' curtime: ' + str(curtime) + ' couponcode: ' + str(couponcode) + ' ')
        conn.close()            
        cursor.close()
        sendCouponMail(sessionid, serverid, cid, couponcode, gmsvrurl, outputfile)
    except Exception, data:
        outputfile.write('Exit: ExecuteSendCoupon Exception--' + str(data) + '\n')
        print Exception, ":", data

def logDBExist(logdbname, outputfile):
    import MySQLdb
    conn = MySQLdb.connect(host='localhost',user='root',passwd='')
    cursor = conn.cursor()
    try :
        sql = "SELECT COUNT(1) FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = '" + logdbname + "'"
        cursor.execute(sql)
        cursor.scroll(0)
        retrow = cursor.fetchone()
        if (retrow[0] == 0):
            return "false"
        else:
            return "true"
    except Exception, data:
        outputfile.write('Exit: LogDBExist Exception--' + str(data) + '\n')
        print Exception, ":", data
        return None

def sendCouponMail(sessionid, serverid, cid, couponcode, gmsvrurl, outputfile):
        print "start send1"
        addMailData = {'sessionid':sessionid, 
                       'server':serverid, 
                       'targetCid':cid,
                       'title':escape(u'活动兑换码') + couponcode, 
                       'description':escape(u'恭喜您获得活动兑换码.\n兑换码为:\n') + couponcode,
                       'reward':[{'type':'gold','num':100}]}        
        addMail = urllib2.urlopen(
                url =  gmsvrurl + r'/mail',
                data = json.dumps(addMailData)
            )
        outputfile.write(' result: ')
        addMailRes = json.loads(addMail.read())
        #记录发放记录        
        json.dump(addMailRes, outputfile)
        outputfile.write('\n')
        

def patchSendCoupon(couponList, restCouponNum, usedCouponNum, starttime, endtime, playerlevel, logdbname, coupondbname, curtime, sessionid, serverid, gmsvrurl, outputfile):
    playerList = getPlayerList(starttime, endtime, logdbname, playerlevel, serverid, outputfile)
    if (len(playerList) > restCouponNum):
        sendNum = restCouponNum        
    else:
        sendNum = len(playerList)
    for i in range(0, sendNum):
        executeSendCoupon(str(playerList[i][0]), str(playerList[i][1]), str(couponList[usedCouponNum + i][0]), coupondbname, curtime, sessionid, serverid, gmsvrurl, outputfile)
    return sendNum

def getAllPlayerList(starttime, logprefix, playerlevel, serverid, dbname, sessionid, gmsvrurl, outputfile):
    import time
    curtime = datetime.strptime(time.strftime("%Y-%m-%d %H:%M:%S",time.localtime()),"%Y-%m-%d %H:%M:%S")
    if (starttime < curtime):        
        startyear = starttime.year
        startmonth = starttime.month
        startday = starttime.day
        curyear = curtime.year
        curmonth = curtime.month
        curday = curtime.day
        if (startyear == curyear and startmonth == curmonth and startday == curday):
            #在同一天            
            couponList = getCouponList(dbname, outputfile)
            restCouponNum = len(couponList)
            usedCouponNum = 0
            
            logdbname = logprefix + starttime.strftime("%Y%m%d")
            if (logDBExist(logdbname, outputfile) == "true"):
                patchSendCoupon(couponList, restCouponNum, usedCouponNum, starttime, curtime, playerlevel, logdbname, curtime, sessionid, serverid, gmsvrurl, outputfile)            
        else:            
            couponList = getCouponList(dbname, outputfile)
            restCouponNum = len(couponList)
            usedCouponNum = 0

            #处理第一天的值            
            logdbname = logprefix + "%04d%02d%02d" %(startyear, startmonth, startday)
            if (logDBExist(logdbname, outputfile) == "true"):
                tempendtime = datetime.strptime(time.strftime("%04d-%02d-%02d %d:%d:%d"%(startyear, startmonth, startday, 23, 59, 59)),"%Y-%m-%d %H:%M:%S")
                sendNum = patchSendCoupon(couponList, restCouponNum, usedCouponNum, starttime, tempendtime, playerlevel, logdbname, dbname, curtime, sessionid, serverid, gmsvrurl, outputfile)
                usedCouponNum = sendNum
                restCouponNum -= sendNum
                if (restCouponNum == 0):
                    return
            
            for i in range(startyear, curyear + 1):
                monthstart = 1
                monthend = 12
                if (i == startyear):
                    monthstart = startmonth
                if (i == curyear):
                    monthend = curmonth
                for j in range(monthstart, monthend + 1):
                    daystart = 1
                    dayend = 31
                    if (i == startyear and j == startmonth):
                        daystart = startday + 1
                    if (i == curyear and j == curmonth):
                        dayend = curday - 1
                    for k in range(daystart, dayend + 1):
                        logdbname = logprefix + "%04d%02d%02d" %(i, j, k)                        
                        if (logDBExist(logdbname, outputfile) == "true"):
                            tempstarttime = datetime.strptime(time.strftime("%04d-%02d-%02d %d:%d:%d"%(i, j, k, 0, 0, 0)),"%Y-%m-%d %H:%M:%S")
                            tempendtime = datetime.strptime(time.strftime("%04d-%02d-%02d %d:%d:%d"%(i, j, k, 23, 59, 59)),"%Y-%m-%d %H:%M:%S")
                            sendNum = patchSendCoupon(couponList, restCouponNum, usedCouponNum, tempstarttime, tempendtime, playerlevel, logdbname, dbname, curtime, sessionid, serverid, gmsvrurl, outputfile)
                            usedCouponNum += sendNum
                            restCouponNum -= sendNum
                            if (restCouponNum == 0):
                                return

            #处理最后一天的值
            if (logDBExist(logdbname, outputfile) == "true"):
                logdbname = logprefix + "%04d%02d%02d" %(curyear, curmonth, curday)
                tempstarttime = datetime.strptime(time.strftime("%04d-%02d-%02d %d:%d:%d"%(curyear, curmonth, curday, 0, 0, 0)),"%Y-%m-%d %H:%M:%S")
                sendNum = patchSendCoupon(couponList, restCouponNum, usedCouponNum, tempstarttime, curtime, playerlevel, logdbname, dbname, curtime, sessionid, serverid, gmsvrurl, outputfile)
                usedCouponNum += sendNum
                restCouponNum -= sendNum
                if (restCouponNum == 0):
                    return
    else:
        return None
    
if __name__ == "__main__":    
    import time
    configfile = open('config.json', 'r')
    config = json.load(configfile)
    configfile.close()
    dbname = config['coupondb']
    logprefix = config['logprefix']
    playerlevel = config['playerlevel']
    serverid = config['serverid']
    starttime = datetime.strptime(config['starttime'],"%Y-%m-%d %H:%M:%S")
    user = config['user']
    password = config['password']
    gmsvrurl = config['gmsvrurl']
    outputfile = open('coupon_result.txt', 'a+')    
    retgm = connectGMServer(user, password, gmsvrurl)    
    if (retgm == ""):
        outputfile.write('Exit : Connect GM Server Fail\n')
    else:        
        ret = createCouponDB(dbname, outputfile)
        if (ret == "true"):            
            retcouponnum = getAvaibleCouponNum(dbname, outputfile)
            if (retcouponnum > 0):
                rettime = getCouponStartTime(dbname, starttime, outputfile)
                if (rettime != None):                    
                    getAllPlayerList(rettime, logprefix, playerlevel, serverid, dbname, retgm, gmsvrurl, outputfile)
            else:
                print "No Coupon Avaialbe"
            
        
        
    













